﻿/******************************************************************
** 文 件 名: BD_Project_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-3-6 11:36:01
** 描    述: 实现公共部分的数据访问封装，提供基础资料-项目信息的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// BD_Project数据访问类
    /// </summary>
	public partial class BD_Project_DAL : BaseDataAccess<BD_Project>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public BD_Project_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public int GetNextSortCode()
        {
            string sql = @"Select max(SortCode) From BD_Project";
            object obj = ExecuteScalar(sql);
            if (obj == DBNull.Value || obj == null) { return 1; }
            return Convert.ToInt32(obj) + 1;
        }

        public DataTable GetSelectSplitProject(string projectCategory, string code, string sn, string name, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DbParameter pProjectCategory = CreateParameter("ProjectCategory");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = projectCategory;
            DbParameter pCode = CreateParameter("Code");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = code;
            DbParameter pSN = CreateParameter("SN");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = sn;
            DbParameter pName = CreateParameter("Name");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = name;
            string sql = string.Format(@"Select * From BD_Project
                Where IsSplit=1 {0} {1} {2} {3}
                And ID Not in 
                (Select ProjectID From BD_ProjectSplit_Detail)",
                                                               string.IsNullOrEmpty(projectCategory) ? "" : string.Format(" And ProjectCategory={0}", pProjectCategory.ParameterName),
                                                               string.IsNullOrEmpty(code) ? "" : string.Format(" AND CODE LIKE '%'+{0}+ '%' ", pCode.ParameterName),
                                                               string.IsNullOrEmpty(sn) ? "" : string.Format(" AND SN LIKE '%'+{0}+ '%' ", pSN.ParameterName),
                                                               string.IsNullOrEmpty(name) ? "" : string.Format(" AND Name LIKE '%'+{0}+ '%' ", pName.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(projectCategory))
            {
                command.Parameters.Add(pProjectCategory);
            }
            if (!string.IsNullOrEmpty(code))
            {
                command.Parameters.Add(pCode);
            }
            if (!string.IsNullOrEmpty(sn))
            {
                command.Parameters.Add(pSN);
            }
            if (!string.IsNullOrEmpty(name))
            {
                command.Parameters.Add(pName);
            }
            return ExecuteDataTable(command, "SortCode,SN", pageSize, currentPageIndex, out pageCount, out recordCount);
        }

        /// <summary>
        /// 得到项目分类的项目数量
        /// </summary>
        /// <returns></returns>
        public DataTable GetProjectCategoryCount(bool? isInit)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"Select A.DictValue ProjectCategory,Count(B.ID) ChildNodes
                From Sys_Dictionary A 
                Left Join BD_Project B On A.DictValue=B.ProjectCategory
                Where A.CategoryID='ProjectCategory'");
            if (isInit.HasValue)
            {
                sb.Append(" And B.IsInit=");
                if (isInit.Value == true)
                {
                    sb.Append("1");
                }
                else
                {
                    sb.Append("0");
                }
            }
            sb.Append(" Group by A.DictValue");
            return ExecuteDataTable(sb.ToString());
        }

        public DataTable GetProjectsByCategory(string category, bool? isInit)
        {
            DbParameter paramerer = CreateParameter("ProjectCategory");
            paramerer.DbType = DbType.String;
            paramerer.Value = category;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"Select ID,SN,Name From BD_Project 
                Where ProjectCategory={0}", paramerer.ParameterName);
            if (isInit.HasValue)
            {
                sb.Append(" And IsInit=");
                if (isInit.Value == true)
                {
                    sb.Append("1");
                }
                else
                {
                    sb.Append("0");
                }
            }
            sb.Append("  Order by SortCode");
            DbCommand command = GetDbCommand(sb.ToString());
            command.Parameters.Add(paramerer);
            return ExecuteDataTable(command);
        }

        public DataTable GetProjectActiveData(string projectId)
        {
            DbParameter parameter = CreateParameter("ProjectID");
            parameter.DbType = DbType.String;
            parameter.Value=projectId;
            DbCommand command = GetStoredProcCommand("Proc_GetProjectActiveData");
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }

        /// <summary>
        /// 得到项目信息完整性
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetProjectComplete(string projectName, string projectCategory, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DbParameter pProjectName = CreateParameter("Name");
            pProjectName.DbType = DbType.String;
            pProjectName.Value = projectName;
            DbParameter pProjectCategory = CreateParameter("ProjectCategory");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = projectCategory;
            string sql = string.Format(@"Select A.ID,A.Name,A.SN,A.Code,A.SortCode,A.Owner971,A.Owner,A.FinInvestment,A.Investment,A.TotalCapitalAmount,
                B.GSChange,
                C.LoanAmount,
                D.GKLoanAmount,
                E.PlanYear,
                F.AddAmount
                From BD_Project A
                Left Join (Select ProjectID,Count(ID) As GSChange From BD_ProjectBudget Group by ProjectID) B On A.ID=B.ProjectID
                Left Join (Select ProjectID,Count(ID) As LoanAmount From BD_ProjectLoanAmount Group by ProjectID) C On A.ID=C.ProjectID
                Left Join (Select ProjectID,Count(ID) As GKLoanAmount From BD_ProjectGKLoanAmount Group by ProjectID) D On A.ID=D.ProjectID
                Left Join (Select ProjectID,Count(ID) As PlanYear From BD_ProjectInvestmentPlan Group by ProjectID) E On A.ID=E.ProjectID
                Left Join (Select ProjectID,Count(ID) As AddAmount From BD_ProjectAddInvestment Group by ProjectID) F On A.ID=F.ProjectID
                Where 1=1 {0} {1}",
                                  string.IsNullOrEmpty(projectName) ? "" : string.Format(" AND A.Name like '%'+{0}+'%'", pProjectName.ParameterName),
                                  string.IsNullOrEmpty(projectCategory) ? "" : string.Format(" And A.ProjectCategory={0}", pProjectCategory.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(projectName)) { command.Parameters.Add(pProjectName); }
            if (!string.IsNullOrEmpty(projectCategory)) { command.Parameters.Add(pProjectCategory); }
            return ExecuteDataTable(command, "SortCode", pageSize, currentPageIndex, out pageCount, out recordCount);
        }

        public bool ConfirmInit(string projectId, string userId, string opinion)
        {
            DbCommand command = GetStoredProcCommand("Proc_ConfirmProject");
            DbParameter pID = CreateParameter("id");
            pID.DbType = DbType.String;
            pID.Value = projectId;
            DbParameter pUserID = CreateParameter("UserID");
            pUserID.DbType = DbType.String;
            pUserID.Value = userId;
            DbParameter pOpinion = CreateParameter("Opinion");
            pOpinion.DbType = DbType.String;
            pOpinion.Value = opinion;
            command.Parameters.Add(pID);
            command.Parameters.Add(pUserID);
            command.Parameters.Add(pOpinion);
            ExecuteNonQuery(command);
            return true;
            //DbParameter pProjectID = CreateParameter("ID");
            //pProjectID.DbType = DbType.String;
            //pProjectID.Value = projectId;
            //DbParameter pUserID = CreateParameter("ConfirmUser");
            //pUserID.DbType = DbType.String;
            //pUserID.Value = userId;
            //DbParameter pTime = CreateParameter("ConfirmTime");
            //pTime.DbType = DbType.DateTime;
            //pTime.Value = DateTime.Now;
            //DbParameter pOpinion = CreateParameter("ConfirmOpinion");
            //pOpinion.DbType = DbType.String;
            //pOpinion.Value = opinion;
            //string sql = string.Format(@"UPDATE BD_Project Set IsInit=1, ConfirmUser={0},ConfirmTime={1},ConfirmOpinion={2} WHERE ID={3}",
            //    pUserID.ParameterName, pTime.ParameterName, pOpinion.ParameterName, pProjectID.ParameterName);
            //DbCommand command = GetDbCommand(sql);
            //command.Parameters.Add(pUserID);
            //command.Parameters.Add(pTime);
            //command.Parameters.Add(pOpinion);
            //command.Parameters.Add(pProjectID);
            //return ExecuteNonQuery(command) == 1;
        }

        public DataTable GetProjectByCode(string code)
        {
            DbParameter parameter = GetDbParameter("Code",code);
            string sql = string.Format(@"Select A.ID,A.SN,A.Code,A.Name,B.CompName OwnerName
                From BD_Project A
                Inner Join Org_Company B On A.Owner=B.ID
                Where A.Code={0} Order By SN", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }
        public DataTable GetProjectBySN(string sn)
        {
            DbParameter parameter2 = GetDbParameter("SN", sn);
            string sql = string.Format(@"Select A.ID,A.SN,A.Code,A.Name,B.CompName OwnerName,A.FCode
                From BD_Project A
                Inner Join Org_Company B On A.Owner=B.ID
                Where A.SN={0}", parameter2.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter2);
            return ExecuteDataTable(command);
        }
        /// <summary>
        /// 得到动态拆分项目的信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public BD_Project GetDymSplitProjectByCode(string code)
        {
            DbParameter parameter = GetDbParameter("Code", code);
            string sql = string.Format(@"Select top 1 *
                From BD_Project 
                Where Code={0} Order by SN DESC", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            DataTable dt = ExecuteDataTable(command);
            if (dt == null || dt.Rows.Count != 1) { return null; }
            List<BD_Project> lsProject = Honestysoft.BaseObject.DataMapping.TableToEntity<BD_Project>(dt);
            return lsProject[0];
        }

        public DataTable QueryProjects(string sn, string category, string name, int pageSize, int currentPageIndex, out int recordCount)
        {
            DbCommand command = GetStoredProcCommand("Proc_GetProjects");
            DbParameter pSN = CreateParameter("sn");
            pSN.DbType = DbType.String;
            pSN.Value = sn;
            DbParameter pOwner971 = CreateParameter("category");
            pOwner971.DbType = DbType.String;
            pOwner971.Value = category;
            DbParameter pName = CreateParameter("name");
            pName.DbType = DbType.String;
            pName.Value = name;
            DbParameter pPageSize = CreateParameter("pageSize");
            pPageSize.DbType = DbType.Int32;
            pPageSize.Value = pageSize;
            DbParameter pCurrentPageIndex = CreateParameter("currentPageIndex");
            pCurrentPageIndex.DbType = DbType.Int32;
            pCurrentPageIndex.Value = currentPageIndex;
            DbParameter pRecordCount = CreateParameter("recordCount");
            pRecordCount.DbType = DbType.Int32;
            pRecordCount.Direction = ParameterDirection.Output;
            command.Parameters.Add(pSN);
            command.Parameters.Add(pOwner971);
            command.Parameters.Add(pName);
            command.Parameters.Add(pPageSize);
            command.Parameters.Add(pCurrentPageIndex);
            command.Parameters.Add(pRecordCount);
            DataTable dt = ExecuteDataTable(command);
            recordCount = Convert.ToInt32(pRecordCount.Value);
            return dt;
        }

        /// <summary>
        /// 获得项目简介
        /// 如果有现场考察，则需要取最后的现场考察记录
        /// </summary>
        /// <param name="sn"></param>
        /// <param name="category"></param>
        /// <param name="name"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetProjectOverview(string sn, string category, string name, int pageSize, int currentPageIndex, out int pageCount,out int recordCount)
        {
            DbParameter pSN = CreateParameter("sn");
            pSN.DbType = DbType.String;
            pSN.Value = sn;
            DbParameter pOwner971 = CreateParameter("category");
            pOwner971.DbType = DbType.String;
            pOwner971.Value = category;
            DbParameter pName = CreateParameter("name");
            pName.DbType = DbType.String;
            pName.Value = name;

            string sql = string.Format(@"Select A.ProjectCategoryName,A.ID,A.SN,A.Name,A.vSort,case when Count(B.ID)=0 then 0 else 1 end IsOverview 
                    From 
                    (Select B.DictName ProjectCategoryName,A.ID,A.SN,Convert(decimal(12,6), replace(replace(A.SN,'(','.'),')','')) vSort,A.Name
                    From BD_Project A Inner Join Sys_Dictionary B On A.ProjectCategory = B.DictValue
                    Where 1=1 {0} {1} {2} )
                    A
                    Left Join 
                    (Select ID,DataID From Sys_Attachment Where AttachmentBuType='{3}'
                    union all
                    Select A.ID,B.ProjectID From Sys_Attachment A Inner Join (
                    Select A.* From Bu_Investigate A Inner Join 
                    (Select ProjectID,Max(InvDate) as InvDate
                    From Bu_Investigate Group by ProjectID) B
                    On A.InvDate=B.InvDate And A.ProjectID=B.ProjectID) B
                    On A.DataID=B.ID                    
                        ) B 
                    On A.ID=B.DataID
                    Group by A.ProjectCategoryName,A.ID,A.SN,A.Name,A.vSort", 
                                                        string.IsNullOrEmpty(sn) ? "" : string.Format(" And A.SN LIKE '%'+{0}+ '%' ", pSN.ParameterName),
                                                        string.IsNullOrEmpty(category) ? "" : string.Format(" And A.ProjectCategory={0}", pOwner971.ParameterName),
                                                        string.IsNullOrEmpty(name) ? "" : string.Format(" And A.Name LIKE '%'+{0}+ '%' ", pName.ParameterName),
                                                        ((int)EnumItem.ProjectDocument.Overview).ToString());
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(sn))
            {
                command.Parameters.Add(pSN);
            }
            if (!string.IsNullOrEmpty(category))
            {
                command.Parameters.Add(pOwner971);
            }
            if (!string.IsNullOrEmpty(name))
            {
                command.Parameters.Add(pName);
            }
            DataTable dt = ExecuteDataTable(command,"vSort",pageSize,currentPageIndex,out pageCount,out recordCount);
            return dt;
        }

        /// <summary>
        /// 刷新作业表的项目信息
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int RefreshATProjectInfo(string projectId,DbTransaction transaction)
        {
            DbParameter parameter = CreateParameter("ProjectID");
            parameter.DbType = DbType.String;
            parameter.Value = projectId;
            DbCommand command = GetStoredProcCommand("Proc_RefreshATProjectInfo");
            command.Parameters.Add(parameter);
            return ExecuteNonQuery(command, transaction);
        }

        public DataTable GetProjectDocumentStatusBySN(string projectSN)
        {
            DbParameter parameter = GetDbParameter("SN",projectSN);
            string sql = string.Format(@"Select A.IsPD1,A.IsPD2,A.IsPD3,A.IsPD4,
A.IsPD5,A.IsPD6,A.IsPD7,A.IsPD8,A.IsPD9,
A.IsPD10
From Bu_AuditTable_Opinion A
Inner Join Bu_AuditTable B
On A.ID=B.ID
Inner Join 
(Select ProjectID,Max(FirstDate) as FirstDate From Bu_AuditTable Group by ProjectID) C
On B.FirstDate=C.FirstDate  And B.ProjectID=C.ProjectID
Inner Join BD_Project D
On B.ProjectID=D.ID
Where D.SN={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }

        public DataTable GetProjectDocumentStatusByCode(string projectCode)
        {
            DbParameter parameter = GetDbParameter("Code", projectCode);
            string sql = string.Format(@"Select A.IsPD1,A.IsPD2,A.IsPD3,A.IsPD4,
A.IsPD5,A.IsPD6,A.IsPD7,A.IsPD8,A.IsPD9,
A.IsPD10
From Bu_AuditTable_Opinion A
Inner Join Bu_AuditTable B
On A.ID=B.ID
Inner Join 
(Select ProjectID,Max(FirstDate) as FirstDate From Bu_AuditTable Group by ProjectID) C
On B.FirstDate=C.FirstDate  And B.ProjectID=C.ProjectID
Inner Join BD_Project D
On B.ProjectID=D.ID
Where D.Code={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }

        public DataTable GetProjectDocumentStatusByID(string projectId)
        {
            DbParameter parameter = GetDbParameter("ID", projectId);
            string sql = string.Format(@"Select A.IsPD1,A.IsPD2,A.IsPD3,A.IsPD4,
A.IsPD5,A.IsPD6,A.IsPD7,A.IsPD8,A.IsPD9,
A.IsPD10
From Bu_AuditTable_Opinion A
Inner Join Bu_AuditTable B
On A.ID=B.ID
Inner Join 
(Select ProjectID,Max(FirstDate) as FirstDate From Bu_AuditTable Group by ProjectID) C
On B.FirstDate=C.FirstDate  And B.ProjectID=C.ProjectID 
Inner Join BD_Project D
On B.ProjectID=D.ID
Where D.ID={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }
    }
}
